package db

import (
	"database/sql"
	"fmt"
	"strings"

	_ "github.com/mattn/go-sqlite3"
)

// 涨跌分布
const ZdfenbuTableName = "zdfenbu"

var ZdfenbuColumns = []string{
	"qdate", "keyint", "value",
}
var ZdfenbuUpdateColumns = []string{
	"value",
}

// 涨跌停数
const ZdtcountTableName = "zdtcount"

var ZdtcountColumns = []string{
	"qdate", "t", "ztc", "dtc",
}
var ZdtcountUpdateColumns = []string{
	"ztc", "dtc",
}

// 封板失败
const FbFailedTableName = "fbfailed"

var FbFailedTColumns = []string{
	"qdate", "t", "c", "zbp",
}
var FbFailedTUpdateColumns = []string{
	"c", "zbp",
}

// 涨停池
const ZtPoolTableName = "ztpool"

var ZtPoolColumns = []string{
	"qdate", "code", "name", "price", "change_pct", //
	"amount", "ltsz", "tshare", "hs", "lbc", //
	"fbt", "lbt", "fund", "zbc", "hybk", //
	"zttj_days", "zttj_ct", "market",
}
var ZtPoolUpdateColumns = []string{
	"name", "price", "change_pct", //
	"amount", "ltsz", "tshare", "hs", "lbc", //
	"fbt", "lbt", "fund", "zbc", "hybk", //
	"zttj_days", "zttj_ct", "market",
}

// 昨日涨停池
const YesPoolTableName = "yes_ztpool"

var YesPoolColumns = []string{
	"qdate", "code", "name", "price", "change_pct", //
	"amount", "ltsz", "tshare", "hs", "yfbt", //
	"ylbc", "hybk", "zttj_days", "zttj_ct", "market", //
}
var YesPoolUpdateColumns = []string{
	"name", "price", "change_pct", //
	"amount", "ltsz", "tshare", "hs", "yfbt", //
	"ylbc", "hybk", "zttj_days", "zttj_ct", "market", //
}

const PositionIndTable = "positionind"

var PositionIndColumns = []string{
	"qdate", "positionind", "title", "content",
}
var PositionIndUpdateColumns = []string{
	"positionind", "title", "content",
}

// gntop
const GnTop10TableName = "gn_top10"

var GnTopColumns = []string{
	"qdate", "market_code", "board_code", "board_type", "board_name", //
	"price", "change_percent", "change_amount", "turnover_rate", "total_market_value", //
	"rising_count", "falling_count", "top_stock_name", "top_stock_rise", "top_stock_code", //
	"f141", "data_source", "lowest_stock_name", "lowest_stock_code", "f209", //
	"lowest_stock_percent",
}

var GnTopUpdateColumns = []string{
	"market_code", "board_type", "board_name", //qdate,board_code
	"price", "change_percent", "change_amount", "turnover_rate", "total_market_value", //
	"rising_count", "falling_count", "top_stock_name", "top_stock_rise", "top_stock_code", //
	"f141", "data_source", "lowest_stock_name", "lowest_stock_code", "f209", //
	"lowest_stock_percent",
}

const GnStockTable = "gnstock"

var GnStockColumns = []string{
	"qdate", "market_code", "code", "name", "market", //
	"price", "change_percent", "board_code", "board_name",
}

var GnStockUpdateColumns = []string{
	"price", "change_percent",
}

func buildInsertSQL(tableName string, columns, updateColumns []string) string {

	// 构造 INSERT 部分
	placeholder := strings.Repeat("?, ", len(columns)-1) + "?"

	// SQLite 不支持 ON DUPLICATE KEY UPDATE，这里使用 INSERT OR REPLACE
	finalSQL := fmt.Sprintf("INSERT OR REPLACE INTO %s (%s) VALUES (%s)", tableName, strings.Join(columns, ", "), placeholder)

	return finalSQL
}

func getDB() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", "stock_data.db")
	if err != nil {
		fmt.Println("无法打开数据库:", err)
		return nil, err
	}

	err = db.Ping()
	if err != nil {
		fmt.Println("无法连接到数据库:", err)
		return nil, err
	}

	return db, err
}

var DBPATH string

func InitDB(dbPath string) {
	DBPATH = dbPath
	sql := `
CREATE TABLE IF NOT EXISTS zdfenbu (
    qdate INTEGER,
    keyint INTEGER,
    value INTEGER,
    PRIMARY KEY (qdate, keyint)
);

CREATE TABLE IF NOT EXISTS zdtcount (
    qdate INTEGER,
    t INTEGER,
    ztc INTEGER,
    dtc INTEGER,
    PRIMARY KEY (qdate, t)
);

CREATE TABLE IF NOT EXISTS fbfailed (
    qdate INTEGER,
    t INTEGER,
    c INTEGER,
    zbp REAL,
    PRIMARY KEY (qdate, t)
);

CREATE TABLE IF NOT EXISTS ztpool (
    qdate INTEGER,
    code TEXT,
    name TEXT,
    price NUMERIC(10, 2),
    change_pct NUMERIC(5, 2),
    amount BIGINT,
    ltsz NUMERIC(15, 2),
    tshare NUMERIC(15, 2),
    hs NUMERIC(5, 2),
    lbc INTEGER,
    fbt INTEGER,
    lbt INTEGER,
    fund NUMERIC(15, 2),
    zbc INTEGER,
    hybk TEXT,
    zttj_days INTEGER,
    zttj_ct INTEGER,
    market INTEGER,
    PRIMARY KEY (qdate, code)
);

CREATE TABLE IF NOT EXISTS yes_ztpool (
    qdate INTEGER,
    code TEXT,
    name TEXT,
    price NUMERIC(10, 2),
    change_pct NUMERIC(5, 2),
    amount BIGINT,
    ltsz NUMERIC(15, 2),
    tshare NUMERIC(15, 2),
    hs NUMERIC(5, 2),
    yfbt INTEGER,
    ylbc INTEGER,
    hybk TEXT,
    zttj_days INTEGER,
    zttj_ct INTEGER,
    market INTEGER,
    PRIMARY KEY (qdate, code)
);

CREATE TABLE IF NOT EXISTS positionind(
    qdate INTEGER,
    positionind INTEGER ,
    title TEXT,
    content TEXT,
    PRIMARY KEY (qdate)
);

CREATE TABLE IF NOT EXISTS gn_top10 (
    qdate INTEGER,
    market_code INTEGER NOT NULL,
    board_code TEXT NOT NULL,
    board_type INTEGER,
    board_name TEXT,
    price NUMERIC(18, 4),
    change_percent NUMERIC(18, 4),
    change_amount INTEGER,
    turnover_rate NUMERIC(18, 4),
    total_market_value BIGINT,
    rising_count INTEGER,
    falling_count INTEGER,
    top_stock_name TEXT,
    top_stock_rise NUMERIC(18, 4),
    top_stock_code TEXT,
    f141 INTEGER,
    data_source INTEGER,
    lowest_stock_name TEXT,
    lowest_stock_code TEXT,
    f209 INTEGER,
    lowest_stock_percent NUMERIC(18, 4),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (qdate, board_code)
);

CREATE TABLE IF NOT EXISTS gnstock(
    qdate INTEGER,
    market_code INTEGER,
    code TEXT,
    name TEXT,
    market INTEGER,
    price  NUMERIC(18,4),
    change_percent  NUMERIC(18,4),
    board_code TEXT,
	board_name TEXT,
    PRIMARY KEY (qdate, board_code,code)
);
`
	db, err := getDB()
	if err != nil {
		fmt.Println("无法连接到数据库:", err)
		return
	}
	defer db.Close()

	db.Exec(sql)
}

func SaveDB(values [][]interface{}, tableName string, columns, updateColumns []string) {
	// 插入或更新封板未遂曲线数据到 SQLite
	db, err := getDB()
	if err != nil {
		fmt.Println("无法连接到数据库:", err)
		return
	}
	defer db.Close()

	prepareSQL := buildInsertSQL(tableName, columns, updateColumns)
	stmt, err := db.Prepare(prepareSQL)
	if err != nil {
		fmt.Println("准备语句失败:", err, prepareSQL)
		return
	}
	defer stmt.Close()

	row := 0
	for i, v := range values {
		_, err := stmt.Exec(v...)
		if err != nil {
			fmt.Printf("执行插入或更新语句失败: %v %s \n%d - %v\n", err, prepareSQL, i, v)
			break
		}
		row++
	}
	fmt.Println("save:", row)
}
